<?php
class Frontend_Table_Query extends Custom_Model_Table_Abstract
{
    protected $_name        = 'query';
    protected $_rowClass    = 'Frontend_Table_Row_Query';

    public function findById($id)
    {
        return $this->find((int)$id)->current();
    }

    protected function _getIncludeCategory($categoryId)
    {
        $id = (int) $categoryId;
        $category = Wenda::getTable('Category')->findByParentId($id)->toArray();

        if (0 == count($category)){
            $arr = array($id);
        } else {
            $arr = array();
            foreach($category as $row) {
                $arr[] = $row['id'];
            }
        }
        return $arr;
    }

    //推荐问题
    public function getRecommend($categoryId=null, $limit=10, $page=null)
    {
        $id = (int) $categoryId;

        $select = $this->_db->select();
        $select->from('query as q')
               ->joinLeft('category as c','q.category_id = c.id', 'c.title as category_title')
               ->joinLeft('user as u','q.user_id = u.id','u.name as user_name')
               ->joinLeft('answer as a','q.id = a.query_id','count(a.query_id) as answer_total');

        if (null !== $categoryId){
            $select->where('q.category_id IN (?)' , $this->_getIncludeCategory($id));
        }

        $select->where('q.status = ?','A')
               ->having('answer_total > 10')
               ->group('q.id')
               ->limit((int)$limit)
               ->order('q.pageviews DESC')
               ->order('answer_total DESC');

        return $this->_db->fetchAll($select);
    }

    //待解决
    public function getIssued($categoryId=null, $limit=10, $page=null)
    {
        $id = (int) $categoryId;

        $select = $this->_db->select();
        $select->from('query as q')
               ->joinLeft('category as c','q.category_id = c.id', 'c.title as category_title')
               ->joinLeft('user as u','q.user_id = u.id','u.name as user_name');

        if (null !== $categoryId){
            $select->where('q.category_id IN (?)' , $this->_getIncludeCategory($id));
        }

        $select->where('q.finish = ?','N')
               ->where('q.status = ?','A')
               ->limit((int)$limit)
               ->order('q.created_at DESC');

        return $this->_db->fetchAll($select);
    }

    //已解决
    public function getResolved($categoryId=null, $limit=10, $page=null)
    {
        $id = (int) $categoryId;

        $select = $this->_db->select();
        $select->from('query as q')
               ->joinLeft('category as c','q.category_id = c.id', 'c.title as category_title')
               ->joinLeft('user as u','q.user_id = u.id','u.name as user_name');

        if (null !== $categoryId){
            $select->where('q.category_id IN (?)' , $this->_getIncludeCategory($id));
        }

        $select->where('q.finish = ?','Y')
               ->where('q.status = ?','A')
               ->limit((int)$limit)
               ->order('q.resolved_at DESC');

        return $this->_db->fetchAll($select);
    }

    //高悬赏问题
    public function getHightReward($categoryId=null, $limit=10, $page=null)
    {
        $id = (int) $categoryId;

        $select = $this->_db->select();
        $select->from('query as q')
               ->joinLeft('category as c','q.category_id = c.id', 'c.title as category_title')
               ->joinLeft('user as u','q.user_id = u.id','u.name as user_name');

        if (null !== $categoryId){
            $select->where('q.category_id IN (?)' , $this->_getIncludeCategory($id));
        }

        $select->where('q.finish = ?','N')
               ->where('q.reward > 0')
               ->where('q.status = ?','A')
               ->limit((int)$limit)
               ->order('q.reward DESC');

        return $this->_db->fetchAll($select);
    }
    
    //零回答问题
    public function getNoAnswer($categoryId=null, $limit=10, $page=null)
    {
        $id = (int) $categoryId;

        $select = $this->_db->select();
        $select->from('query as q')
               ->joinLeft('category as c','q.category_id = c.id', 'c.title as category_title')
               ->joinLeft('user as u','q.user_id = u.id','u.name as user_name')
               ->joinLeft('answer as a','q.id = a.query_id','count(a.query_id) as answer_total');

        if (null !== $categoryId){
            $select->where('q.category_id IN (?)' , $this->_getIncludeCategory($id));
        }

        $select->where('q.status = ?','A')
               ->having('answer_total = 0')
               ->group('q.id')
               ->limit((int)$limit)
               ->order('q.created_at DESC');
        
        return $this->_db->fetchAll($select);
    }
}